﻿using ICSharpCode.SharpZipLib.Zip;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;

namespace Hukee.ExcelHelper.NPOIExtensions
{
    /// <summary>
    /// Excel导入帮助类
    /// </summary>
    public class ExcelHelper
    {
        /// <summary>
        /// 根据导入的Excel文件流，获取对应的 Excel导入类
        /// </summary>
        /// <typeparam name="T">输出Dto对象（该类必须要有 IExcelImportClass 实现标记）</typeparam>
        /// <param name="file">Excel文件流</param>
        /// <param name="fileName">Excel文件名称</param>
        /// <returns></returns>
        public static List<T> GetDataListByExcel<T>(Stream file, string fileName) where T : IExcelImportDto, new()
        {
            // 只读取Excel首页sheet
            ISheet sheet = GetWorkbookByExcelFileType(file, fileName)?.GetSheetAt(0);

            // 初始化 标题 与 属性列 关系字典
            var props = typeof(T).GetProperties();
            var rfHelper = new ReflectionHelper();
            var excelPropertyDic = InitExcelPropertyDic(sheet,props, rfHelper);
            if (excelPropertyDic.Keys.Count <= 0) return new List<T>();

            // 读取Excel数据转为List
            var list = GetExcelDataToList<T>(sheet,excelPropertyDic,rfHelper);

            file.Flush();
            file.Close();
            return list;
        }

        /// <summary>
        /// 根据导入的Excel文件流，获取对应的 Excel导入类
        /// </summary>
        /// <typeparam name="T">输出Dto对象（该类必须要有 IExcelImportClass 实现标记）</typeparam>
        /// <param name="file">Excel文件流</param>
        /// <param name="fileName">Excel文件名称</param>
        /// <returns></returns>
        public static List<T> GetDataListByExcel<T>(Stream file, ExcelVersion excelVersion) where T : IExcelImportDto, new()
        {
            file.Seek(0, SeekOrigin.Begin);
            IWorkbook workBook;
            if (excelVersion == ExcelVersion.xls) workBook = new HSSFWorkbook(file);
            else workBook = new XSSFWorkbook(file);

            // 只读取Excel首页sheet
            ISheet sheet = workBook?.GetSheetAt(0);

            // 初始化 标题 与 属性列 关系字典
            var props = typeof(T).GetProperties();
            var rfHelper = new ReflectionHelper();
            var excelPropertyDic = InitExcelPropertyDic(sheet, props, rfHelper);
            if (excelPropertyDic.Keys.Count <= 0) return new List<T>();

            // 读取Excel数据转为List
            var list = GetExcelDataToList<T>(sheet, excelPropertyDic, rfHelper);

            file.Close();
            return list;
        }

        /// <summary>
        /// 获取excel数据
        /// </summary>
        private static List<T> GetExcelDataToList<T>(ISheet sheet, Dictionary<int, PropertyInfo> excelPropertyDic, ReflectionHelper rfHelper) where T : IExcelImportDto, new()
        {
            List<T> list = new List<T>();
            //获取excel数据
            for (int i = 1; i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row == null || row.Cells.Count < excelPropertyDic.Keys.Count)
                    break;
                T data = new T();
                foreach (var v in excelPropertyDic)
                {
                    var cell = row.GetCell(v.Key);
                    var cellValue = GetCellStrValue(cell);
                    try
                    {
                        rfHelper.SetValue(cellValue, v.Value, data);
                    }
                    catch (Exception e) // 属性里throw的异常会被覆盖一层，从而取不到目标异常
                    {
                        if (e.InnerException != null) throw e.InnerException;
                        else throw e;
                    }

                }
                list.Add(data);
            }
            return list;
        }

        /// <summary>
        /// 初始化 表头首行标题列数 与 ExcelImportColumn标识的属性 对应关系
        /// </summary>
        /// <param name="sheet">首页</param>
        /// <param name="props">属性列</param>
        /// <param name="rfHelper">反射帮助类</param>
        /// <returns></returns>
        private static Dictionary<int, PropertyInfo> InitExcelPropertyDic(ISheet sheet, PropertyInfo[] props, ReflectionHelper rfHelper)
        {
            var excelPropertyDic = new Dictionary<int, PropertyInfo>();
            //获取首行数据头
            var head = sheet?.GetRow(0);
            if (head == null) return excelPropertyDic;
            int index = 0;
            foreach (var cell in head)
            {
                var propInfo = rfHelper.GetPropertyByExcelImportColumnName(props, GetCellStrValue(cell).Trim());
                if (propInfo != null) excelPropertyDic.Add(index, propInfo);
                index++;
            }
            return excelPropertyDic;
        }

        /// <summary>
        /// 根据Excel版本不同 生成对应的Workbook
        /// </summary>
        /// <param name="file">文件流</param>
        /// <param name="fileName">文件名</param>
        /// <returns></returns>
        private static IWorkbook GetWorkbookByExcelFileType(Stream file, string fileName)
        {
            string fileExt = Path.GetExtension(fileName).ToLower();
            file.Seek(0, SeekOrigin.Begin);
            if (fileExt == ".xls") return new HSSFWorkbook(file);
            else if ((fileExt == ".xlsx")) return new XSSFWorkbook(file);
            else throw new Exception($"当前格式尾识别为'{fileExt}'，目前仅支持文件格式为 .xls | .xlsx 的Excel文件");
        }

        /// <summary>
        /// 获取Cell里的值（以字符串的形式获取）
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static string GetCellStrValue(ICell cell)
        {
            var cellValue = string.Empty;
            if (cell != null)
            {
                switch (cell.CellType)
                {
                    case CellType.Numeric:
                        cellValue = cell.NumericCellValue.ToString();
                        break;
                    case CellType.String:
                    case CellType.Blank:
                        cellValue = cell.StringCellValue.ToString();
                        break;
                    case CellType.Boolean:
                        cellValue = cell.BooleanCellValue.ToString();
                        break;
                    case CellType.Error:
                        cellValue = cell.ErrorCellValue.ToString();
                        break;
                    case CellType.Formula:
                        cellValue = cell.NumericCellValue.ToString();
                        break;
                    default:
                        throw new Exception("Excel Cell格式未能识别，无法读取值");
                }
            }
            return cellValue;
        }

    }

    public static class NPOIExtensions
    {
        public static byte[] ExcelDataByteZipToOneFileBytes(ICollection<ExcelSheetData> excelDataList)
        {
            if (excelDataList == null || excelDataList.Count == 0) return null;
            using (MemoryStream st = new MemoryStream())
            {
                //ZipOutputStream类的构造函数需要一个流，文件流、内存流都可以，压缩后的内容会写入到这个流中。
                using (ZipOutputStream zipStream = new ZipOutputStream(st))
                {
                    foreach (var v in excelDataList)
                    {
                        var data = new ExcelData(new List<ExcelSheetData> { v });
                        var dataBuffer = ToExcelFileBytes(data, false);
                        //获取excel字节并添加到压缩文件
                        ZipEntry zipEntry = new ZipEntry(v.ExcelSheetName);
                        zipEntry.DateTime = DateTime.Now;
                        zipEntry.IsUnicodeText = true;
                        zipStream.PutNextEntry(zipEntry);
                        zipStream.Write(dataBuffer, 0, dataBuffer.Length);
                    }
                    zipStream.CloseEntry();
                    //使用流操作时一定要设置IsStreamOwner为false。否则很容易发生在文件流关闭后的异常。
                    zipStream.IsStreamOwner = false;
                    zipStream.Finish();
                    zipStream.Close();
                }
                var buffer = st.GetBuffer();
                st.Close();
                return buffer;
            }
        }
        
        /// <summary>
        /// 导出Excel字节数据,字节可用作文件流输出 *列表所属类 必须有[ExcelFile]与[ExcelColumn]特性修饰过
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entities">必须有[ExcelFile]与[ExcelColumn]特性修饰过的类列表</param>
        /// <param name="needMerge">是否合并单元格</param>
        /// <param name="needToZip">是否压缩</param>
        /// <returns></returns>
        public static byte[] ToExcelBytesByNPOI<T>(this ICollection<T> entities, bool needMerge = false, bool needToZip = true) where T : class
        {
            if (entities == null || entities.Count == 0) throw new Exception("输入的参数列表不能为空");
            var excelResult = entities.ToExcelSheetData();
            excelResult.NeedMerge = needMerge;
            ExcelData data = new ExcelData(new List<ExcelSheetData> { excelResult });
            return ToExcelFileBytes(data, needToZip);
        }

        /// <summary>
        /// 导出Excel字节数据
        /// </summary>
        /// <returns></returns>
        public static byte[] ToExcelBytesByNPOI(this ExcelData excelData,bool needToZip = false)
        {
            return ToExcelFileBytes(excelData, needToZip);
        }

        private static byte[] ToExcelFileBytes(ExcelData excelData, bool needToZip = true)
        {
            byte[] buffer = null;
            IWorkbook workbook = new XSSFWorkbook();
            var sheetItem = excelData.GetSheetList();

            // 默认标题列样式设置
            IFont font = workbook.CreateFont();
            font.FontName = "宋体";
            font.IsBold = true;
            ICellStyle substyle = workbook.CreateCellStyle();
            substyle.Alignment = HorizontalAlignment.Left;
            substyle.VerticalAlignment = VerticalAlignment.Center;
            substyle.BorderLeft = BorderStyle.Thin;
            substyle.BorderRight = BorderStyle.Thin;
            substyle.SetFont(font);

            // 默认数据列样式设置
            ICellStyle rowStyle = workbook.CreateCellStyle();
            rowStyle.BorderBottom = BorderStyle.Thin;
            rowStyle.BorderLeft = BorderStyle.Thin;
            rowStyle.BorderRight = BorderStyle.Thin;
            rowStyle.BorderTop = BorderStyle.Thin;
            rowStyle.Alignment = HorizontalAlignment.Left;
            rowStyle.VerticalAlignment = VerticalAlignment.Center;

            var sheetNameDic = new Dictionary<string, int>();
            foreach (var excelResult in sheetItem)
            {
                var sheetName = string.Empty;
                if (sheetNameDic.ContainsKey(excelResult.ExcelSheetName))
                {
                    sheetName = excelResult.ExcelSheetName + "_" + sheetNameDic[excelResult.ExcelSheetName]++;
                }
                else { sheetName = excelResult.ExcelSheetName; sheetNameDic.Add(sheetName, 1); }

                ISheet sheet = workbook.CreateSheet(sheetName); // 创建一个分页

                //计算单元格最大长度，方便后面适配宽度
                int[] cellMaxWidth = new int[excelResult.ColumnLength()];
                ASCIIEncoding ascii = new ASCIIEncoding();

                //标题列输入
                IRow rowHead = sheet.CreateRow(0);
                var styleDic = excelResult.ExcelStyleDic;
                var normalStyleDic = new Dictionary<int, ICellStyle>();
                for (int j = 0; j < excelResult.ColumnLength(); j++)
                {
                    ICell cell = rowHead.CreateCell(j, CellType.String);
                    cell.CellStyle = substyle;
                    if (styleDic != null && styleDic.ContainsKey(j))
                    {
                        foreach (var v in styleDic[j])
                        {
                            var style = GetCellStyleByExcelStyle(workbook, v);
                            if ((v.ToFormat & FormatType.Title) == FormatType.Title)
                            {
                                cell.CellStyle = style;
                            }
                            if ((v.ToFormat & FormatType.Normal) == FormatType.Normal)
                            {
                                normalStyleDic.Add(j, style);
                                break;
                            }
                        }
                    }
                    //表头保留字符串格式
                    var value = excelResult[0, j].ToString();
                    cell.SetCellValue(value);
                    cellMaxWidth[j] = StrLengthByASCII(value, ascii);
                }

                //数据列输入
                for (int i = 1; i < excelResult.RowLength(); i++)
                {
                    rowHead = sheet.CreateRow(i);
                    for (int j = 0; j < excelResult.ColumnLength(); j++)
                    {
                        ICell cell = rowHead.CreateCell(j, CellType.String);
                        if (normalStyleDic.ContainsKey(j)) cell.CellStyle = normalStyleDic[j];
                        else cell.CellStyle = rowStyle;
                        var value = excelResult[i, j];
                        //cell.SetCellValue(value);

                        //var length = StrLengthByASCII(value, ascii);
                        //这边还能继续优化
                        if (value is int || value is double || value is float || value is decimal)
                        {
                            cell.SetCellValue(Convert.ToDouble(value));
                        }
                        else
                        {
                            cell.SetCellValue(value.ToString());
                            // 如果值不是数字类型，作为普通字符串设置

                        }
                        var length = StrLengthByASCII(value.ToString(), ascii);
                        if (cellMaxWidth[j] < length)
                        {
                            cellMaxWidth[j] = length;
                        }
                    }
                }

                //设置自适应宽度，并限制最大宽度。宽度过大Excel会报错
                for (int i = 0; i < cellMaxWidth.Length; i++)
                {
                    sheet.SetColumnWidth(i, Math.Min(cellMaxWidth[i] + 1, 30) * 270);
                }

                //合并列生成
                if (excelResult.NeedMerge) excelResult.MergeInfos.ForEach(v => sheet.AddMergedRegion(new CellRangeAddress(v.X, v.X_Merge, v.Y, v.Y_Merge)));
            }

            //转为字节数组  
            using (MemoryStream stream = new MemoryStream())
            {
                workbook.Write(stream);
                buffer = stream.GetBuffer();
            }

            if (needToZip) buffer = BytesToZip(buffer, excelData.ExcelFileName);
            return buffer;
        }
        
        private static ICellStyle GetCellStyleByExcelStyle(IWorkbook workbook,ExcelStyle excelStyle)
        {
            IFont fontFormate = workbook.CreateFont();
            fontFormate.FontName = excelStyle.FontName;
            fontFormate.IsBold = excelStyle.IsBold;
            fontFormate.IsItalic = excelStyle.IsItalic;
            fontFormate.IsStrikeout = excelStyle.IsStrikeout;
            fontFormate.FontHeightInPoints = excelStyle.FontSize;

            if (excelStyle.ExcelUnderLineType != ExcelUnderLineType.None) fontFormate.Underline = (FontUnderlineType)excelStyle.ExcelUnderLineType;
            // 默认数据列样式设置
            ICellStyle rowStyle = workbook.CreateCellStyle();
            rowStyle.BorderBottom = BorderStyle.Thin;
            rowStyle.BorderLeft = BorderStyle.Thin;
            rowStyle.BorderRight = BorderStyle.Thin;
            rowStyle.BorderTop = BorderStyle.Thin;
            rowStyle.Alignment = HorizontalAlignment.Left;
            rowStyle.VerticalAlignment = VerticalAlignment.Center;
            rowStyle.SetFont(fontFormate);

            return rowStyle;
        }

        private static byte[] BytesToZip(byte[] buffer, string fileName)
        {
            using (MemoryStream st = new MemoryStream())
            {
                //ZipOutputStream类的构造函数需要一个流，文件流、内存流都可以，压缩后的内容会写入到这个流中。
                using (ZipOutputStream zipStream = new ZipOutputStream(st))
                {
                    //获取excel字节并添加到压缩文件
                    ZipEntry zipEntry = new ZipEntry(fileName);
                    zipEntry.DateTime = DateTime.Now;
                    zipEntry.IsUnicodeText = true;
                    zipStream.PutNextEntry(zipEntry);
                    zipStream.Write(buffer, 0, buffer.Length);
                    zipStream.CloseEntry();
                    //使用流操作时一定要设置IsStreamOwner为false。否则很容易发生在文件流关闭后的异常。
                    zipStream.IsStreamOwner = false;
                    zipStream.Finish();
                    zipStream.Close();
                }
                buffer = st.GetBuffer();
                st.Close();
                return buffer;
            }
        }

        /// <summary>
        /// 得到字符串长度，一个汉字长度为2
        /// </summary>
        /// <param name="inputString">参数字符串</param>
        /// <returns></returns>
        private static int StrLengthByASCII(string inputString, ASCIIEncoding ascii)
        {
            int tempLen = 0;
            byte[] s = ascii.GetBytes(inputString);
            for (int i = 0; i < s.Length; i++)
            {
                if (s[i] == 63)
                    tempLen += 2;
                else
                    tempLen += 1;
            }
            return tempLen;
        }

    }

    public enum ExcelVersion
    {
        xls = 0,
        xlsx = 1
    }
}
